﻿#include "databasequery.h"
#include "DataBase/database.h"
#include <QStringList>
#include <QDebug>
#include <QSqlError>
#include <QMessageBox>
#include "Dialog/mymessagedialog.h"
#include "common.h"

DataBaseQuery::DataBaseQuery(QSqlDatabase db):QSqlQuery(db)
{
}
bool DataBaseQuery::createIndex(const QString &tabName, const QString &fieldName, const QString &indexName)
{
    QString indexStr = indexName;
    if(indexStr == "")
        indexStr = DataBaseQuery::indexName(fieldName);
    QString str;
    str="drop index "+indexStr+" on "+tabName;
    exec(str);
    str="create index "+indexStr+" on "+tabName+"("+fieldName+")";
    return execQString(str);
}

QString DataBaseQuery::indexName(const QString &name)
{
    return "index_" + name;
}

bool DataBaseQuery::addColumn(QString tabName,QString column,QString option)
{
    QString str = "ALTER TABLE "+ tabName +" ADD " + column +" "+option;
    return exec(str);
}

bool DataBaseQuery::checkIfHaveRecord(QString tableName, QString column, QString data)
{
    DataBaseQuery query;
    QString str;
    str="SELECT 1 "
            " FROM "+tableName+
            " WHERE "+tableName+"."+column+" = "+data+
            " LIMIT 1";
    query.execQString(str);
    if(query.next())
        return true;
    else
        return false;
}

bool DataBaseQuery::selectTable(QString tabName,const QStringList *keyList, QString option,QString shortItem,bool shortdesc,QString limit)
{
    QString str = "select ";
    if(keyList==NULL)
    {
        str+="* ";
    }
    else
    {
        int i = 0;
        for(i=0;i<keyList->count()-1;i++)
        {
            str+=keyList->at(i);
            str+=',';
        }
        str+=keyList->at(i);

    }
    str+=" from ";
    str+=tabName;
    if(option.length()!=0)
    {
        if((option.contains("where")||option.contains("WHERE")||option.contains("join")||option.contains("JOIN"))==false)
            str+=" where " +option;
        else
            str+=option;
    }
    if(shortItem.length()!=0)
    {
        str+=" order by LOWER(";
        str+=shortItem;
        str+=")";
        if(shortdesc)
        {
            str+= " desc";
        }
        else
        {
            str+= " asc";
        }
    }

    if(limit.length()!=0)
    {
        str+=" limit "+limit;
    }
    if(exec(str)==false)
    {
        errCrashRepairHandle(lastError().databaseText(),lastError().driverText());
#if 0
        //        MyMessageDialog *mDialog= new MyMessageDialog(lastError().databaseText()+'\n'+lastError().driverText(),QObject::tr("DataBase Error"));
        //        mDialog->hideCancelBtn();
        //        int ret=mDialog->exec();
        //qDebug()<<lastError();
#endif
        return false;
    }
    else
    {
        qDebug() << "!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!  query str:" <<str;
    }
    return true;
}

bool DataBaseQuery::selectTableJoin(QString tabName, const QStringList *keyList, const QStringList *joinList,QString option, QString shortItem, bool shortdesc, QString limit)
{
    QString str = "select ";
    if(keyList==NULL)
    {
        str+="* ";
    }
    else
    {
        int i = 0;
        for(i=0;i<keyList->count()-1;i++)
        {
            str+=keyList->at(i);
            str+=',';
        }
        str+=keyList->at(i);

    }
    str+=" from ";
    str+=tabName;
#if 0
    //    if(joinList.length()!=0)
    //    {
    //        str+=" "+joinList;
    //    }
#endif
    if(joinList!=NULL)
    {
        int i=0;
        for(;i<joinList->count()-1;i++)
        {
            str+=" "+joinList->at(i);
            str+= " ";
        }
        str+=" "+joinList->at(i);
    }
    if(option.length()!=0)
    {
        str+=" where " +option;
    }
    if(shortItem.length()!=0)
    {
        str+=" order by LOWER(";
        str+=shortItem;
        str+=")";
        if(shortdesc)
        {
            str+= " desc";
        }
        else
        {
            str+= " asc";
        }
    }
    if(limit.length()!=0)
    {
        str+=" limit "+limit;
    }
    if(exec(str)==false)
    {
        errCrashRepairHandle(lastError().databaseText(),lastError().driverText());
#if 0
        //        MyMessageDialog *mDialog= new MyMessageDialog(lastError().databaseText()+'\n'+lastError().driverText(),QObject::tr("DataBase Error"));
        //        mDialog->hideCancelBtn();
        //        int ret=mDialog->exec();
#endif
        return false;
    }

    return true;
}

bool DataBaseQuery::selectTableJoin(QString tabName, const QStringList *keyList, const QStringList *joinList,QString option, QString shortItem, bool isAlphabet,bool shortdesc, QString limit)
{
    QString str = "select ";
    if(keyList==NULL)
    {
        str+="* ";
    }
    else
    {
        int i = 0;
        for(i=0;i<keyList->count()-1;i++)
        {
            str+=keyList->at(i);
            str+=',';
        }
        str+=keyList->at(i);
    }
    str+=" from ";
    str+=tabName;
#if 0
    //    if(joinList.length()!=0)
    //    {
    //        str+=" "+joinList;
    //    }
#endif
    if(joinList!=NULL)
    {
        int i=0;
        for(;i<joinList->count()-1;i++)
        {
            str+=" "+joinList->at(i);
            str+= " ";
        }
        str+=" "+joinList->at(i);
    }
    if(option.length()!=0)
    {
        str+=" where " +option;
    }
    if(shortItem.length()!=0)
    {
        if(isAlphabet)
        {
            str+=" order by LOWER(";
        }
        else
        {
            str+=" order by(";
        }
        str+=shortItem;
        str+=")";
        if(shortdesc)
        {
            str+= " desc";
        }
        else
        {
            str+= " asc";
        }
    }
    if(limit.length()!=0)
    {
        str+=" limit "+limit;
    }
    if(exec(str)==false)
    {
        errCrashRepairHandle(lastError().databaseText(),lastError().driverText());
#if 0
        //        MyMessageDialog *mDialog= new MyMessageDialog(lastError().databaseText()+'\n'+lastError().driverText(),QObject::tr("DataBase Error"));
        //        mDialog->hideCancelBtn();
        //        int ret=mDialog->exec();
#endif
        return false;
    }
    return true;
}

bool DataBaseQuery::selectTable(QStringList *tabList,const QStringList *keyList,QString option)
{
    int i;
    QString str = "select ";
    if(tabList==NULL)
        return false;
    if(keyList==NULL)
    {
        str+="* ";
    }
    else
    {
        for(i=0;i<keyList->count()-1;i++)
        {
            str+=keyList->at(i);
            str+=',';
        }
        str+=keyList->at(i);

    }
    str+=" from ";
    for(i=0;i<tabList->count()-1;i++)
    {
        str+=tabList->at(i);
        str+=',';
    }
    str+=tabList->at(i);
    if(option.length()!=0)
    {
        if((option.contains("where")||option.contains("WHERE")||option.contains("join")||option.contains("JOIN"))==false)
            str+=" where " +option;
        else
            str+=option;
    }

    if(exec(str)==false)
    {
        errCrashRepairHandle(lastError().databaseText(),lastError().driverText());
#if 0
        //        MyMessageDialog *mDialog= new MyMessageDialog(lastError().databaseText()+'\n'+lastError().driverText(),QObject::tr("DataBase Error"));
        //        mDialog->hideCancelBtn();
        //        int ret=mDialog->exec();
#endif
        return false;
    }
    return true;
}
bool DataBaseQuery::insert(QString tabName,const QMap<QString,QVariant> &dataMap)
{
    QString str = "insert into " +tabName + " (";
    QString valueStr = "values (";
    int i;
    QStringList keyList = dataMap.keys();
    qDebug() << "\n\nkeyList: " << keyList;
    QVariantList valueList = dataMap.values();
    qDebug() << "\n\nvalueList: " << valueList;
    for(i=0;i<keyList.count()-1;i++)
    {
        str+=keyList.at(i);
        str+=',';
        valueStr += "?";
        valueStr += ',';
    }

    str+=keyList.at(i);
    str+=") ";
    valueStr += "?";
    valueStr += ')';
    str+=valueStr;
    prepare(str);
    for(i=0;i<keyList.count();i++)
    {
        addBindValue(valueList.at(i));
    }

    if(exec()==false)
    {
//        errCrashRepairHandle(lastError().databaseText(),lastError().driverText());
#if 1
                MyMessageDialog *mDialog= new MyMessageDialog(lastError().databaseText()+'\n'+lastError().driverText(),QObject::tr("DataBase Error"));
                mDialog->hideCancelBtn();
                int ret=mDialog->exec();
#endif
        return false;
    }
    return true;
}

bool DataBaseQuery::insertOrUpdate(const QString &tabName, const QString &keyName, QMap<QString, QVariant> &dataMap)
{

    QString keyValue = dataMap.value(keyName).toString();
    QString str = "select " + keyName + " from " + tabName + " where " + keyName + " = " + keyValue;
    this->execQString(str);
    if(this->first())
    {
        this->updateItem(tabName,keyName,keyValue,dataMap);
    }
    else
    {
        this->insert(tabName,dataMap);
    }
    return true;
}

bool DataBaseQuery::deleteItem(QString tabName,QString option)
{
    QString str = "DELETE";
    str+=" from ";
    str+= tabName;
    if(option.length()!=0)
        str+=" where " +option;
    if(exec(str)==false)
    {
//        errCrashRepairHandle(lastError().databaseText(),lastError().driverText());
                MyMessageDialog *mDialog= new MyMessageDialog(lastError().databaseText()+'\n'+lastError().driverText(),QObject::tr("DataBase Error"));
                mDialog->hideCancelBtn();
                mDialog->exec();
                qDebug()<<lastError();
    }
    return true;
}

bool DataBaseQuery::deleteItem(const QString &tabName,const QString &fieldName,const QString &da)
{
    QString str = "DELETE";
    str+=" from ";
    str+= tabName;
    str+=" where " +fieldName+" = "+ da;
    if(exec(str)==false)
    {
        errCrashRepairHandle(lastError().databaseText(),lastError().driverText());
#if 0
        //        MyMessageDialog *mDialog= new MyMessageDialog(lastError().databaseText()+'\n'+lastError().driverText(),QObject::tr("DataBase Error"));
        //        mDialog->hideCancelBtn();
        //        mDialog->exec();
        //        qDebug()<<lastError();
#endif
        return false;
    }
    return true;
}

bool DataBaseQuery::updateItem(QString tabName,QMap<QString,QVariant> &dataMap,QString option)
{
    QString str = "update " +tabName + " SET ";
    int i;
    QStringList keyList = dataMap.keys();
    QVariantList valueList = dataMap.values();
    for(i=0;i<keyList.count()-1;i++)
    {
        str+=keyList.at(i);
        str+='=';
        str += "?";
        str += ',';
    }
    str+=keyList.at(i);
    str+="=";
    str += "?";
    if(option.length()!=0)
        str+=" where " +option;
    prepare(str);

    for(i=0;i<keyList.count();i++)
    {
        addBindValue(valueList.at(i));
    }    
    if(exec()==0)
    {
        errCrashRepairHandle(lastError().databaseText(),lastError().driverText());
#if 0
        //        MyMessageDialog *mDialog= new MyMessageDialog(lastError().databaseText()+'\n'+lastError().driverText(),QObject::tr("DataBase Error"));
        //        mDialog->hideCancelBtn();
        //        int ret=mDialog->exec();
#endif
        return false;
    }
    return true;
}

bool DataBaseQuery::updateItem(QString tabName, QString fieldName, QString now, const QMap<QString, QVariant> &dataMap_new)
{
    QString str="UPDATE "+tabName+" SET ";
    int i;
    QStringList keyList = dataMap_new.keys();
    QVariantList valueList = dataMap_new.values();
    for(i=0;i<keyList.count()-1;i++)
    {
        str+=keyList.at(i);
        str+='=';
        str += "?";
        str += ',';
    }
    str+=keyList.at(i);
    str+="=";
    str += "?";
    if(now.count()!=0)
        str+=" WHERE "+fieldName+" = "+now;
    prepare(str);

    for(i=0;i<keyList.count();i++)
    {
        addBindValue(valueList.at(i));
    }
    if(exec()==0)
    {
        errCrashRepairHandle(lastError().databaseText(),lastError().driverText());
#if 0
        //        MyMessageDialog *mDialog= new MyMessageDialog(lastError().databaseText()+'\n'+lastError().driverText(),QObject::tr("DataBase Error"));
        //        mDialog->hideCancelBtn();
        //        int ret=mDialog->exec();
#endif
        return false;
    }
    return true;
}

bool DataBaseQuery::errCrashRepairHandle(QString dbtext, QString sqltext,QString retry)
{
#if 0
//    if(dbtext.contains("is marked as crashed and should be repaired") ||
//            dbtext.contains("try to repair it"))
//    {
//        QStringList list = dbtext.split("'");
//        QString tabname = list.at(1).split("/").last();
//        if(list.size() == 3)
//        {
//            if(exec("REPAIR TABLE Market."+tabname+";") == true)
//            {
//                if(!retry.isEmpty() && exec(retry) == true)
//                {
//                    return true;
//                }
//                else
//                {
//                    return false;
//                }
//            }
//        }
//        MyMessageDialog *mDialog= new MyMessageDialog(dbtext+'\n'+sqltext,QObject::tr("DataBase Error"));
//        mDialog->hideCancelBtn();
//        int ret=mDialog->exec();
//        Q_UNUSED(ret);
//        qDebug()<<lastError();
//        return true;
//    }
//    else
//    {
//        MyMessageDialog *mDialog= new MyMessageDialog(dbtext+'\n'+sqltext,QObject::tr("DataBase Error"));
//        mDialog->hideCancelBtn();
//        int ret=mDialog->exec();
//        Q_UNUSED(ret);
//        qDebug()<<lastError();
//        return false;
//    }
#endif
}

bool DataBaseQuery::dropDataBaseTableData(QString table)
{
    QString str;//删除
    bool r=true;
    str="drop TABLE "  + table;
    if(exec(str)==false)
    {
        r=false;
    }
    return r;
}

bool DataBaseQuery::deleteDataBaseTableData(QString table)
{
    QString str;//清空
    bool r=true;
    str="DELETE FROM "  + table+" where true;";
    if(exec(str)==false)
    {
        r=false;
    }
    return r;
}

bool DataBaseQuery::resetDataBaseTableData(QString table)
{
    QString str;  //清空且复位ID
    bool r=true;
#ifdef USE_MYSQL
    str="TRUNCATE TABLE "+table;
#else
    str="DELETE FROM "  + table;
#endif
    if(exec(str)==false)
    {
        r=false;
    }
    return r;
}

bool DataBaseQuery::unionSelectTabs(const QStringList *keyList, QStringList *tabList, QString option, QString shortItem, bool shortdesc)
{
    QString str;
    str+="select DISTINCT ";
    if(keyList==NULL)
    {
        str+="* ";
    }
    else
    {
        int i;
        for(i=0;i<keyList->count()-1;i++)
        {
            str+=keyList->at(i);
            str+=',';
        }
        str+=keyList->at(i);

    }
    str+=" from ";
    str+=" (";

    for(int j=0;j<tabList->count();j++)
    {
        str+= "select DISTINCT ";
        str+=" * ";
        str+=" from ";
        str+=tabList->at(j);
        if(j!=tabList->count()-1)
        {
            str+=" UNION ALL ";
        }
    }
    str+=" )";
    str+="as a"; // MySQL needs
    if(option.length()!=0)
    {
        str+=" where " +option;
    }
    if(shortItem.length()!=0)
    {
        QStringList list=shortItem.split(',');
        int t = list.size();
        str+=" order by  ";
        for(int i=0;i<t-1;i++)
        {
            str+=list.at(i);
            str+=",";
        }
        str+=list.at(t-1);

        if(shortdesc)
        {
            str+= " desc";
        }
        else
        {
            str+= " asc";
        }
    }
    if(exec(str)==false)
    {
        errCrashRepairHandle(lastError().databaseText(),lastError().driverText()/*,str*/);//before 2017-11-08 donot have this
#if 0
        //        QMessageBox::critical(NULL,"Error",  //MARK20170923 REMOVE
        //                              lastError().databaseText()
        //                              +'\n'
        //                              +lastError().driverText());
        ////qDebug()<<lastError();
#endif
        return false;
    }
    return true;
}

bool DataBaseQuery::getMax(QString table, QString fieldName)
{
    QString str = "select DISTINCT ";
    str+=   " max( "+fieldName+")";
    str+=   " as "+fieldName;
    str+=   " from ";
    str+=   table;
    exec(str);
    return true;
}

int DataBaseQuery::size()
{
    if(last())
    {
        return at()+1;
    }
    return 0;
}

bool DataBaseQuery::execQString(const QString &str)
{
    qDebug() << "=====str: " << str;

    if(exec(str)==false)
    {

        errCrashRepairHandle(lastError().databaseText(),lastError().driverText()/*,str*/);
#if 0
        //        MyMessageDialog *mDialog= new MyMessageDialog(lastError().databaseText()+'\n'+lastError().driverText(),QObject::tr("DataBase Error"));
        //        mDialog->hideCancelBtn();
        //        mDialog->exec();
#endif
        return false;
    }
    else
    {
        return true;
    }
}

bool DataBaseQuery::execQString_test(const QString &str)
{
    Q_UNUSED(str);
    return false;
}

bool DataBaseQuery::removeTable(const QString &table)
{
    QString str="DROP TABLE "+table;
    return this->execQString(str);
}

bool DataBaseQuery::deleteDataBaseTable(QString database, QString table)
{
    if(database==DATABASE_NAME)
    {
        DataBaseQuery query;
        QString str;
        bool r=false;
        str="DELETE FROM ["+table+"]";
        if(query.execQString(str)==false)
        {
            r=false;
        }
        return r;
    }
    else
    {
        DataBaseQuery query;
        QString str;
        bool r=false;
        str="DELETE FROM ["+table+"]";
        if(query.execQString(str)==false)
        {
            r=false;
        }
        return r;
    }
}

bool DataBaseQuery::isItemExist(const QString &tabName,const QString &item,const QVariant &content)
{
    QString str = "select DISTINCT ";
    str+=item;
    str+=" from ";
    str+=tabName;
    str+=" where "+ item + "=?";
    prepare(str);
    addBindValue(content);
    if(exec()==0){
        errCrashRepairHandle(lastError().databaseText(),lastError().driverText());
#if 0
        //        MyMessageDialog *mDialog= new MyMessageDialog(lastError().databaseText()+'\n'+lastError().driverText(),QObject::tr("DataBase Error"));
        //        mDialog->hideCancelBtn();
        //        mDialog->exec();
        //        qDebug()<<"@#%$@#$="<<lastError();
#endif
        return false;
    }

    if(this->first())
        return true;
    return false;
}
